package com.manager.plat.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.IBalanceDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.vo.BalanceVo;
/**
 * 业务结算数据实现层
 * @author liugeng
 *
 */
public class BalanceDaoImpl extends JdbcDaoSupport implements IBalanceDao {
	private static Logger logger = Logger.getLogger(BalanceDaoImpl.class);
	/**根据查询条件获得通道结算信息*/
	@SuppressWarnings("unchecked")
	@Override
	public List<BalanceVo> getGatewayBalance(int gateway_id, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		
		Pattern date_pattern = Pattern.compile("[0-9]*");
		String date_format_comm;
		logger.info(String.format("time_begin:[%s], time_end:[%s]", time_begin, time_end));
		if(date_pattern.matcher(time_begin).matches() && date_pattern.matcher(time_end).matches()) {
			date_format_comm = "'%Y%m%d'";
		}
		else {
			date_format_comm = "'%Y-%m-%d'";
		}
		logger.info(String.format("date_format_comm:[%s]", date_format_comm));
		
		String sql = "select g.sp_number, count(1) fee_user_num, g.fee_type, ceiling(g.fee_code/100) fee_code, sum(ub.minute) fee_minute, " 
					+"ceiling(sum(case when g.fee_type = 1 then (ub.minute * g.fee_code/100) when g.fee_type = 2 then (g.fee_code/100) else 0 end)) fee_price "
					+"from tb_ivr_user_bill ub "
					+"inner join tb_ivr_gateway g on g.id = ub.gateway_id " 
					+"where date_format(str_to_date(ub.time_begin," + date_format_comm + "),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end," + date_format_comm + "),'%Y-%m-%d') <= ? ";
		if(gateway_id != 0 ){
			sql += "and g.id = ? ";
			paramList.add(gateway_id);
		}
		sql += "group by g.sp_number, g.fee_type, g.fee_code";
		Object[] param = paramList.toArray();
		
		logger.info(String.format("根据查询条件获得通道结算信息[%s]", BaseUtil.logSQL(sql, param)));
		List<BalanceVo> balanceVoList = this.getJdbcTemplate().query(sql, param, rowMapper);
		if(balanceVoList != null && balanceVoList.size() > 0){
			return balanceVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<BalanceVo> rowMapper = new ParameterizedRowMapper<BalanceVo>() {
		@Override
		public BalanceVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			BalanceVo balanceVo = new BalanceVo();
			balanceVo.setSp_number(rs.getString("sp_number"));
			balanceVo.setFee_user_num(rs.getInt("fee_user_num"));
			balanceVo.setFee_type(rs.getInt("fee_type"));
			balanceVo.setFee_code(rs.getInt("fee_code"));
			balanceVo.setFee_minute(rs.getInt("fee_minute"));
			balanceVo.setFee_price(rs.getInt("fee_price"));
			return balanceVo;
		}
	};
	
	/**根据查询条件获得业务结算信息*/
	@SuppressWarnings("unchecked")
	@Override
	public List<BalanceVo> getRouterBalance(int gateway_id, int lower_id, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		
		Pattern date_pattern = Pattern.compile("[0-9]*");
		String date_format_comm;
		logger.info(String.format("time_begin:[%s], time_end:[%s]", time_begin, time_end));
		if(date_pattern.matcher(time_begin).matches() && date_pattern.matcher(time_end).matches()) {
			date_format_comm = "'%Y%m%d'";
		}
		else {
			date_format_comm = "'%Y-%m-%d'";
		}
		logger.info(String.format("date_format_comm:[%s]", date_format_comm));
		
		String sql = "select ub.sp_number, pl.name lower_name, ub.isfilter, count(1) fee_user_num, g.fee_type, ceiling(g.fee_code/100) fee_code, sum(ub.minute) fee_minute,  " 
					+"ceiling(sum(case when g.fee_type = 1 then (ub.minute * g.fee_code/100) when g.fee_type = 2 then (g.fee_code/100) else 0 end)) fee_price "
					+"from tb_ivr_user_bill ub "
					+"inner join tb_ivr_router r on r.id = ub.router_id "
					+"inner join tb_ivr_partner_lower pl on pl.id = r.lower_id "
					+"inner join tb_ivr_gateway g on g.id = ub.gateway_id "
					+"where date_format(str_to_date(ub.time_begin," + date_format_comm + "),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end," + date_format_comm + "),'%Y-%m-%d') <= ? ";
		if(gateway_id != 0){
			sql += "and g.id = ? ";
			paramList.add(gateway_id);
		}
		if(lower_id != 0){
			sql += "and pl.id = ? ";
			paramList.add(lower_id);
		}
		sql += "group by ub.sp_number, pl.name, ub.isfilter, g.fee_type, g.fee_code ";
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得通道结算信息[%s]", BaseUtil.logSQL(sql, param)));
		
		List<BalanceVo> balanceVoList = this.getJdbcTemplate().query(sql, param, tempMapper);
		if(balanceVoList != null && balanceVoList.size() > 0){
			return balanceVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<BalanceVo> tempMapper = new ParameterizedRowMapper<BalanceVo>() {
		@Override
		public BalanceVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			BalanceVo balanceVo = new BalanceVo();
			balanceVo.setSp_number(rs.getString("sp_number"));
			balanceVo.setLower_name(rs.getString("lower_name"));
			balanceVo.setIsfilter(rs.getInt("isfilter"));
			balanceVo.setFee_user_num(rs.getInt("fee_user_num"));
			balanceVo.setFee_type(rs.getInt("fee_type"));
			balanceVo.setFee_code(rs.getInt("fee_code"));
			balanceVo.setFee_minute(rs.getInt("fee_minute"));
			balanceVo.setFee_price(rs.getInt("fee_price"));
			return balanceVo;
		}
	};
	
	/**根据查询条件获得渠道业务结算信息*/
	@SuppressWarnings("unchecked")
	@Override
	public List<BalanceVo> getRouterBalancePartner(int userId, int gateway_id, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(userId);
		
		Pattern date_pattern = Pattern.compile("[0-9]*");
		String date_format_comm;
		logger.info(String.format("time_begin:[%s], time_end:[%s]", time_begin, time_end));
		if(date_pattern.matcher(time_begin).matches() && date_pattern.matcher(time_end).matches()) {
			date_format_comm = "'%Y%m%d'";
		}
		else {
			date_format_comm = "'%Y-%m-%d'";
		}
		logger.info(String.format("date_format_comm:[%s]", date_format_comm));
		
		String sql = "select ub.sp_number, count(1) fee_user_num, g.fee_type, ceiling(g.fee_code/100) fee_code, sum(ub.minute) fee_minute, " 
					+"ceiling(sum(case when g.fee_type = 1 then (ub.minute * g.fee_code/100) when g.fee_type = 2 then (g.fee_code/100) else 0 end)) fee_price "
					+"from tb_ivr_user_bill ub "
					+"inner join tb_ivr_router r on r.id = ub.router_id "
					+"inner join tb_ivr_partner_lower pl on pl.id = r.lower_id "
					+"inner join tb_ivr_gateway g on g.id = ub.gateway_id "
					+"where date_format(str_to_date(ub.time_begin," + date_format_comm + "),'%Y-%m-%d') >= ? and date_format(str_to_date(ub.time_end,"+date_format_comm+"),'%Y-%m-%d') <= ? "
					+"and pl.user_id = ? and ub.isfilter = 0 ";
		if(gateway_id != 0){
			sql += "and g.id = ? ";
			paramList.add(gateway_id);
		}
		sql += "group by ub.sp_number, g.fee_type, g.fee_code ";
		Object[] param = paramList.toArray();
		logger.info(String.format("根据查询条件获得渠道业务结算信息[%s]", BaseUtil.logSQL(sql, param)));
		
		List<BalanceVo> balanceVoList = this.getJdbcTemplate().query(sql, param, tempPartnerMapper);
		if(balanceVoList != null && balanceVoList.size() > 0){
			return balanceVoList;
		}
		
		return null;
	}
	
	/**数据转换*/
	private ParameterizedRowMapper<BalanceVo> tempPartnerMapper = new ParameterizedRowMapper<BalanceVo>() {
		@Override
		public BalanceVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			BalanceVo balanceVo = new BalanceVo();
			balanceVo.setSp_number(rs.getString("sp_number"));
			balanceVo.setFee_user_num(rs.getInt("fee_user_num"));
			balanceVo.setFee_type(rs.getInt("fee_type"));
			balanceVo.setFee_code(rs.getInt("fee_code"));
			balanceVo.setFee_minute(rs.getInt("fee_minute"));
			balanceVo.setFee_price(rs.getInt("fee_price"));
			return balanceVo;
		}
	};
}
